<%-- 
    Document   : query1
    Created on : 2014/2/26, 上午 11:53:06
    Author     : Gjun
--%>

<%@page import="java.util.HashMap"%>
<%@page import="java.util.ArrayList"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%
    String year = request.getParameter("year");
    String month = request.getParameter("month");
    if(year.equals("") || month.equals("")){
        request.getRequestDispatcher("oops.jsp").forward(request, response);
    }
    db.SQLC sqc = new db.SQLC();
    String sql = "select 商品名稱,sum(商品數量) as 銷售量 ,sum(商品價格*商品數量) as 銷售額  from 訂單清單 where datepart (year,訂購日期)="+year+" and datepart (month,訂購日期)="+month+" group by 商品名稱 order by 銷售量 desc";
    ArrayList<HashMap> list = sqc.query(sql);
%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%=year%>&nbsp;&nbsp;年&nbsp;&nbsp;<%=month%>&nbsp;&nbsp;月商品銷售額排名
        <table border="2">
            <thead>
                <tr>
                    <td>商品名稱</td><td>銷售量</td><td>銷售額</td>
                </tr>
            </thead>
            <tbody>
          <%for(int i=0;i<list.size();i++)
            {
                HashMap map = list.get(i);%>
                <tr>
                    <td><%=map.get("商品名稱")%></td><td><%=map.get("銷售量")%></td><td><%=map.get("銷售額")%></td>
                </tr>
         <%}%>
            </tbody>
        </table>
            <br/>
            <a href="<%=response.encodeURL("OrdersMaintain.jsp") %>">回到訂單查詢</a>
    </body>
</html>
